﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Diagnostics;

namespace ConsoleApp6
{
    public static class Sql
    {
        public static void Add(SqlConnection connection)//添加
        {
            Console.Write("请输入用户名：");
            string sname = Console.ReadLine();
            connection.Open();
            string cx = "select name from Users";
            SqlCommand aa = new SqlCommand(cx, connection);
            var cxx = aa.ExecuteReader();
            bool Uum = true;
            while (cxx.Read())//判断用户名是否重复
            {
                if (sname.Equals(cxx[0]))
                {
                    Uum = false;
                    break;
                }
                else
                {
                    Uum = true;
                }
            }
            if (Uum)
            {
                connection.Close();
                Console.Write("请输入密码：");
                string spassword = Console.ReadLine();
                if (spassword.Length >= 6)//判断密码是否够6位数
                {
                    //打开数据库
                    connection.Open();
                    string tsql = $"insert into Users (Name,Pasword) values ('{sname}','{spassword}')";
                    SqlCommand command = new SqlCommand(tsql, connection);
                    command.ExecuteNonQuery();
                    connection.Close();
                    Console.WriteLine("添加成功！");
                }
                else
                {
                    Console.WriteLine("密码不足6位！");
                }
            }
            else
            {
                connection.Close();
                Console.WriteLine("用户名已存在无法添加！");
            }
        }
        public static void Delete(SqlConnection connection)//删除
        {
            Console.Write("请输入用户名：");
            string sname = Console.ReadLine();
            connection.Open();
            string cx = "select * from Users";
            SqlCommand aa = new SqlCommand(cx, connection);
            var cxx = aa.ExecuteReader();
            bool Uum = false;
            while (cxx.Read())//判断用户名是否存在
            {
                if (sname.Equals(cxx[1]))
                {
                    Uum = true;
                    break;
                }
                else
                {
                    Uum = false;
                }
            }
            if (Uum)
            {
                Console.Write("请输入密码：");
                string spassword = Console.ReadLine();
                if(spassword.Equals(cxx[2]))
                {
                    connection.Close();
                    //打开数据库
                    connection.Open();
                    string tsql = $"DELETE FROM Users WHERE Name='{sname}'";
                    SqlCommand command = new SqlCommand(tsql, connection);
                    command.ExecuteNonQuery();
                    connection.Close();
                    Console.WriteLine("删除成功！");
                }
                else
                {
                    connection.Close();
                    Console.WriteLine("密码不正确！");
                }
                
            }
            else
            {
                connection.Close();
                Console.WriteLine("用户名不存在！");
            }
        }
        public static void Alter(SqlConnection connection)//修改
        {
            Console.Write("请输入用户名：");
            string sname = Console.ReadLine();
            connection.Open();
            string cx = "select * from Users";
            SqlCommand aa = new SqlCommand(cx, connection);
            var cxx = aa.ExecuteReader();
            bool Uum = true;
            while (cxx.Read())//判断用户名是否存在
            {
                if (sname.Equals(cxx[1]))
                {
                    Uum = true;
                    break;
                }
                else
                {
                    Uum = false;
                }
            }
            if (Uum)
            {
                Console.Write("请输入原密码：");
                string spassword = Console.ReadLine();
                if (spassword.Equals(cxx[2]))
                {
                    connection.Close();
                    //打开数据库
                    Console.WriteLine("请输入新密码：");
                    string xpwd= Console.ReadLine();
                    connection.Open();
                    string tsql = $"UPDATE Users SET Pasword='{xpwd}' WHERE Name='{sname}'";
                    SqlCommand command = new SqlCommand(tsql, connection);
                    command.ExecuteNonQuery();
                    connection.Close();
                    Console.WriteLine("修改成功！");
                }
                else
                {
                    Console.WriteLine("输入的密码不匹配！");
                }
            }
            else
            {
                Console.WriteLine("用户名不存在！");
                connection.Close();
            }
        }
        public static void Query(SqlConnection connection)//查询
        {
            connection.Open();
            string tsql = "select * from Users";
            SqlCommand command = new SqlCommand(tsql, connection);
            var print = command.ExecuteReader();
            Console.WriteLine($"ID\t用户名\t密码");
            while (print.Read())
            {
                Console.WriteLine($"{print[0]}\t{print[1]}\t{print[2]}");
            }
            connection.Close();
            Console.WriteLine("查询成功！");
        }

    }
}
